package org.springblade.modules.notice.constant;

/**
 * @Author ysq
 * @Date 2024/11/28
 * @see org.springblade.modules.notice.enums.PushObjectEnum
 * @Description 推送对象常量对应的SQL 规则：P_[推送对象类型code]
 */
public interface PushObjectConstant {

	/**
	 * 任务执行人
	 */
	String P_1 = "SELECT\n" +
		"\tt2.id AS userId,\n" +
		"\tt2.phone,\n" +
		"\tt3.registration_id AS registrationId,\n" +
		"\tt4.openid,\n" +
		"\tt5.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_maintain_plan_content_user t6 ON t1.id = t6.plan_id \n" +
		"\tAND t6.is_deleted = 0\n" +
		"\tLEFT JOIN blade_user t2 ON t6.user_id = t2.id\n" +
		"\tLEFT JOIN blade_user_app_push t3 ON t6.user_id = t3.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t4 ON t6.user_id = t4.user_id \n" +
		"\tAND t4.is_deleted = 0 \n" +
		"\tAND t4.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t5 ON t6.user_id = t5.user_id \n" +
		"\tAND t5.is_deleted = 0 \n" +
		"\tAND t5.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 任务创建人
	 */
	String P_2 = "SELECT\n" +
		"\tt2.id AS userId,\n" +
		"\tt2.phone,\n" +
		"\tt3.registration_id AS registrationId,\n" +
		"\tt4.openid,\n" +
		"\tt5.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tLEFT JOIN blade_user t2 ON t1.create_user = t2.id\n" +
		"\tLEFT JOIN blade_user_app_push t3 ON t2.id = t3.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t4 ON t2.id = t4.user_id \n" +
		"\tAND t4.is_deleted = 0 \n" +
		"\tAND t4.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t5 ON t2.id = t5.user_id \n" +
		"\tAND t5.is_deleted = 0 \n" +
		"\tAND t5.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 工单电梯负责组-维保组长
	 */
	String P_3 = "SELECT\n" +
		"\tt3.id AS userId,\n" +
		"\tt3.phone,\n" +
		"\tt5.registration_id AS registrationId,\n" +
		"\tt6.openid,\n" +
		"\tt7.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_elevator_principal_dept t2 ON t1.elevator_id = t2.elevator_id \n" +
		"\tAND t2.is_deleted = 0\n" +
		"\tINNER JOIN blade_user t3 ON FIND_IN_SET( t2.dept_id, t3.dept_id ) > 0 \n" +
		"\tAND t3.STATUS = 1 \n" +
		"\tAND t3.is_deleted = 0\n" +
		"\tINNER JOIN blade_role t4 ON FIND_IN_SET( t4.id, t3.role_id ) > 0 \n" +
		"\tAND t4.role_alias = 'maintenance_group_leader'\n" +
		"\tLEFT JOIN blade_user_app_push t5 ON t3.id = t5.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t6 ON t3.id = t6.user_id \n" +
		"\tAND t6.is_deleted = 0 \n" +
		"\tAND t6.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t7 ON t3.id = t7.user_id \n" +
		"\tAND t7.is_deleted = 0 \n" +
		"\tAND t7.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 工单审批人
	 */
	String P_4 = "SELECT\n" +
		"\tt3.id AS userId,\n" +
		"\tt3.phone,\n" +
		"\tt5.registration_id AS registrationId,\n" +
		"\tt6.openid,\n" +
		"\tt7.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_business_approve t2 ON t1.id = t2.work_order_id \n" +
		"\tAND t2.is_deleted = 0\n" +
		"\tINNER JOIN blade_user t3 ON FIND_IN_SET( t3.id, t2.approve_ids ) > 0 \n" +
		"\tAND t3.STATUS = 1 \n" +
		"\tAND t3.is_deleted = 0\n" +
		"\tLEFT JOIN blade_user_app_push t5 ON t3.id = t5.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t6 ON t3.id = t6.user_id \n" +
		"\tAND t6.is_deleted = 0 \n" +
		"\tAND t6.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t7 ON t3.id = t7.user_id \n" +
		"\tAND t7.is_deleted = 0 \n" +
		"\tAND t7.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 工单电梯管理人
	 */
	String P_5 = "SELECT\n" +
		"\tt2.id AS userId,\n" +
		"\tt2.phone,\n" +
		"\tt3.registration_id AS registrationId,\n" +
		"\tt4.openid,\n" +
		"\tt5.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_estate_elevator t6 ON t1.elevator_id = t6.elevator_id \n" +
		"\tAND t6.is_deleted = 0\n" +
		"\tINNER JOIN blade_estate_elevator_user t7 ON t6.elevator_id = t7.elevator_id \n" +
		"\tAND t6.tenant_id = t7.tenant_id \n" +
		"\tAND t7.is_deleted = 0\n" +
		"\tLEFT JOIN blade_user t2 ON t7.user_id = t2.id\n" +
		"\tLEFT JOIN blade_user_app_push t3 ON t7.user_id = t3.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t4 ON t7.user_id = t4.user_id \n" +
		"\tAND t4.is_deleted = 0 \n" +
		"\tAND t4.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t5 ON t7.user_id = t5.user_id \n" +
		"\tAND t5.is_deleted = 0 \n" +
		"\tAND t5.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 救援工单项目管理人
	 */
	String P_6 = "SELECT\n" +
		"\tt2.id AS userId,\n" +
		"\tt2.phone,\n" +
		"\tt3.registration_id AS registrationId,\n" +
		"\tt4.openid,\n" +
		"\tt5.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_malfunction_rescue t1\n" +
		"\tINNER JOIN blade_building_contacts t6 ON t1.building_id = t6.building_id \n" +
		"\tAND t6.is_deleted = 0\n" +
		"\tINNER JOIN blade_user t7 ON t6.phone = t7.phone \n" +
		"\tAND t6.tenant_id = t7.tenant_id \n" +
		"\tAND t7.is_deleted = 0\n" +
		"\tLEFT JOIN blade_user t2 ON t7.id = t2.id\n" +
		"\tLEFT JOIN blade_user_app_push t3 ON t7.id = t3.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t4 ON t7.id = t4.user_id \n" +
		"\tAND t4.is_deleted = 0 \n" +
		"\tAND t4.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t5 ON t7.id = t5.user_id \n" +
		"\tAND t5.is_deleted = 0 \n" +
		"\tAND t5.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.plan_id = ?";

	/**
	 * 工单电梯负责组-维保主管
	 */
	String P_7 = "SELECT\n" +
		"\tt3.id AS userId,\n" +
		"\tt3.phone,\n" +
		"\tt5.registration_id AS registrationId,\n" +
		"\tt6.openid,\n" +
		"\tt7.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_elevator_principal_dept t2 ON t1.elevator_id = t2.elevator_id \n" +
		"\tAND t2.is_deleted = 0\n" +
		"\tINNER JOIN blade_user t3 ON FIND_IN_SET( t2.dept_id, t3.dept_id ) > 0 \n" +
		"\tAND t3.STATUS = 1 \n" +
		"\tAND t3.is_deleted = 0\n" +
		"\tINNER JOIN blade_role t4 ON FIND_IN_SET( t4.id, t3.role_id ) > 0 \n" +
		"\tAND t4.role_alias = 'supervisor'\n" +
		"\tLEFT JOIN blade_user_app_push t5 ON t3.id = t5.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t6 ON t3.id = t6.user_id \n" +
		"\tAND t6.is_deleted = 0 \n" +
		"\tAND t6.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t7 ON t3.id = t7.user_id \n" +
		"\tAND t7.is_deleted = 0 \n" +
		"\tAND t7.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 客服
	 */
	String P_8 = "SELECT\n" +
		"\tt2.id AS userId,\n" +
		"\tt2.phone,\n" +
		"\tt3.registration_id AS registrationId,\n" +
		"\tt4.openid,\n" +
		"\tt5.openid AS estateOpenid \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tINNER JOIN blade_role t6 ON t6.role_alias = 'waiter' \n" +
		"\tAND t1.tenant_id = t6.tenant_id \n" +
		"\tAND t6.is_deleted = 0\n" +
		"\tINNER JOIN blade_user t2 ON FIND_IN_SET( t6.id, t2.role_id ) \n" +
		"\tAND t2.`status` = 1 \n" +
		"\tAND t2.is_deleted = 0\n" +
		"\tLEFT JOIN blade_user_app_push t3 ON t2.id = t3.user_id\n" +
		"\tLEFT JOIN blade_user_weixin t4 ON t2.id = t4.user_id \n" +
		"\tAND t4.is_deleted = 0 \n" +
		"\tAND t4.subscribe = 1\n" +
		"\tLEFT JOIN blade_user_weixin_estate t5 ON t2.id = t5.user_id \n" +
		"\tAND t5.is_deleted = 0 \n" +
		"\tAND t5.subscribe = 1 \n" +
		"WHERE\n" +
		"\tt1.id = ?";
}
